from sqlalchemy.orm import sessionmaker
from sqlalchemy import and_, or_

from db import engine
from models import User

Session = sessionmaker(bind=engine)
session = Session()


# 插入数据
async def create_user(name: str, age: int): # 使用 session.add() 添加新数据，commit() 提交
    new_user = User(name="Alice", age=25)
    session.add(new_user)
    session.commit()
    return await session.commit()


# 查询数据
async def get_user(user_id: int):
    """filter() 方式类似 SQL WHERE 语句！"""
    # await session.query(User).all()

    # 年龄 > 20 且姓名是 Alice
    # users = session.query(User).filter(and_(User.age > 20, User.name == "Alice")).all()

    # 年龄 > 20 或 姓名是 Alice
    # users = session.query(User).filter(or_(User.age > 20, User.name == "Alice")).all()

    user = session.query(User).filter_by(name="Alice").first()
    return user


# 更新数据
async def update_user(user_id: int, new_age: int):
    """直接修改对象属性，再 commit() 保存"""
    user = await session.query(User).filter_by(name="Alice").first()
    user.age = new_age
    await session.commit()


# 删除数据
async def delete_user(user_id: int):
    user = session.query(User).filter_by(name="Alice").first()
    session.delete(user)
    await session.commit()